package ServerControl;

import entities.*;

import java.sql.*;
import java.util.LinkedList;

import Server.ServerConnectionGUI;

import ocsf.server.*;
import Common.Settings;


	
public class DBHandler {
	
	
	//Fields
	
	private Client client=null;
	private Picture picture=null;
	private Employee employee =null;
	private Complaint complaint=null;
	private LinkedList<Complaint> complaints;
	private String DBHost;
	private String DBUserName;
	private String DBPassword;
	private String DBScheme ;
	private Connection con;
	private ServerConnectionGUI serverConnectionGUI;
	
	//C'tor & D'tor
	
	public DBHandler(String DB_Host,String DB_UserName, String DB_Password, String DB_Scheme ,ServerConnectionGUI serverConnectionGUI) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
		
		this.serverConnectionGUI = serverConnectionGUI ;
		this.DBHost = DB_Host ;
		this.DBUserName = DB_UserName ;
		this.DBPassword = DB_Password ;
		this.DBScheme = DB_Scheme;
		
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		con = DriverManager.getConnection("jdbc:mysql://"+DBHost+"/"+DB_Scheme,DB_UserName,DB_Password);
		serverConnectionGUI.display("created DB connection : " + con.toString());
		resetAllUsersStatus();
		
	}


	//Methods
	
	public SystemUser getLoginDataByUserNameAndPassword(String userName,String password) {
		Client loginMsg =  new Client(); 
		try 
		{
			PreparedStatement statment = con.prepareStatement("SELECT * FROM Client WHERE UserName=?");
			statment.setString(1,userName);
			ResultSet result = statment.executeQuery();
			if (result.next()){
				String userNameFromDB = result.getString(2);
				String passwordFromDB = result.getString(3);
				String statusFromDB = result.getString(3);
				if (userNameFromDB.equals(userName) && passwordFromDB.equals(password) ){
					if(statusFromDB.equals("1")){ 
						loginMsg.setMsgResponse(MessageManager.msgResponse.USER_IS_ALREADY_CONNECTED);
						return loginMsg ;
					}
					else{
						
						loginMsg.setMsgResponse(MessageManager.msgResponse.SUCCESS);
						//update the DB that user is logged in .
						statment = con.prepareStatement("Update Status From Client  WHERE UserName=?");
						loginMsg.setStatus("1");   
						loginMsg.setFistName(result.getString(3));
						loginMsg.setLastName(result.getString(4));
						loginMsg.setUserEmail(result.getString(5));
						loginMsg.setUserMobile(result.getString(6));
						loginMsg.setUserName(result.getString(2));
						loginMsg.setUserId(result.getInt(1));
						result.close();
						return loginMsg ;
					}
				}
				else{
					
					loginMsg.setMsgResponse(MessageManager.msgResponse.BAD_USERNAME_OR_PASSWORD);
					return loginMsg ; 
				}
				
			}
		
		}catch(SQLException ex){
			
			serverConnectionGUI.display("SQLException: " + ex.getMessage());
			serverConnectionGUI.display("SQLState: " + ex.getSQLState());
			serverConnectionGUI.display("VendorError: " + ex.getErrorCode());
		
		}catch(Exception ex){
			
			serverConnectionGUI.display(ex.getMessage());
		}
		return loginMsg;
	}

	
	public MessageManager.msgResponse SetLogOut(String userName) throws SQLException {
		

			PreparedStatement statment = con.prepareStatement("Update Client SET Status='0' WHERE UserName=?");
			statment.setString(1,userName);
			int  affected = statment.executeUpdate();
			
			if (affected==1){
				return MessageManager.msgResponse.SUCCESS;
			}
			else{
				return MessageManager.msgResponse.FAILED;
			}

	}
	
	public void resetAllUsersStatus() 
	{
		try 
		{
			String sql = " UPDATE Client SET Status='0' " ;
			PreparedStatement statment = con.prepareStatement(sql);
			statment.executeUpdate();
			
		} catch(SQLException ex){
			
			serverConnectionGUI.display("SQLException: " + ex.getMessage());
			serverConnectionGUI.display("SQLState: " + ex.getSQLState());
			serverConnectionGUI.display("VendorError: " + ex.getErrorCode());
			
		}catch(Exception ex){
		
			serverConnectionGUI.display(ex.getMessage());
		}
	}

	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	public Client ClientIDCheck(int clientID){
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
			PreparedStatement statment = con.prepareStatement("SELECT * FROM Client WHERE ClientID=? ");
			statment.setInt(1,clientID);
			ResultSet result = statment.executeQuery();
			if (result.next()){
				client = new Client(result.getString(2),result.getString(3),result.getString(4),result.getString(5),result.getString(6),result.getString(7),result.getString(8));
				client.setUserId(result.getInt(1));
				result.close();
			}
			
			}catch(SQLException ex){
				
				serverConnectionGUI.display("SQLException: " + ex.getMessage());
				serverConnectionGUI.display("SQLState: " + ex.getSQLState());
				serverConnectionGUI.display("VendorError: " + ex.getErrorCode());
			
			}catch(Exception ex){
				
				System.out.println(ex.getMessage());
			}
		return client;	
	}
	public Client FirstNameClientCheck(String firstName){
		
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
			PreparedStatement statment = con.prepareStatement("SELECT * FROM Client WHERE FirstName=? ");
			statment.setString(1,firstName);
			ResultSet result = statment.executeQuery();
			if (result.next()){
				client = new Client(result.getString(2),result.getString(3),result.getString(4),result.getString(5),result.getString(6),result.getString(7),result.getString(8));
				client.setUserId(result.getInt(1));
				result.close();
			}
			
			}catch(SQLException ex){
				
				serverConnectionGUI.display("SQLException: " + ex.getMessage());
				serverConnectionGUI.display("SQLState: " + ex.getSQLState());
				serverConnectionGUI.display("VendorError: " + ex.getErrorCode());
			
			}catch(Exception ex){
				
				System.out.println(ex.getMessage());
			}
		return client;	
	}
	
public Client LastNameClientCheck(String lastName){
		
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
			PreparedStatement statment = con.prepareStatement("SELECT * FROM Client WHERE LastName=? ");
			statment.setString(1,lastName);
			ResultSet result = statment.executeQuery();
			if (result.next()){
				client = new Client(result.getString(2),result.getString(3),result.getString(4),result.getString(5),result.getString(6),result.getString(7),result.getString(8));
				client.setUserId(result.getInt(1));
				result.close();
			}
			
			}catch(SQLException ex){
				
				System.out.println("SQLException: " + ex.getMessage());
		        System.out.println("SQLState: " + ex.getSQLState());
		        System.out.println("VendorError: " + ex.getErrorCode());
			
			}catch(Exception ex){
				
				System.out.println(ex.getMessage());
			}
		return client;	
	}

public Picture PictureIDCheck(int pictureID){
	
	try {
		
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement("SELECT * FROM Picture WHERE PictureID=? ");
		statment.setInt(1,pictureID);
		ResultSet result = statment.executeQuery();
		if (result.next()){
			picture = new Picture(result.getString(2),result.getString(3));
			picture.setPictureID(result.getInt(1));
			result.close();
		}
		
		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
		}
	return picture;	
}

public Picture PicturePlaceCheck(String place){
	
	try {
		
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement("SELECT * FROM Picture WHERE Place=? ");
		statment.setString(1,place);
		ResultSet result = statment.executeQuery();
		if (result.next()){
			picture = new Picture(result.getString(2),result.getString(3));
			picture.setPictureID(result.getInt(1));
			result.close();
		}
		
		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
		}
	return picture;	
}

public Picture PictureTimeCheck(String time){
	
	try {
		
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement("SELECT * FROM Picture WHERE Time=? ");
		statment.setString(1,time);
		ResultSet result = statment.executeQuery();
		if (result.next()){
			picture = new Picture(result.getString(2),result.getString(3));
			picture.setPictureID(result.getInt(1));
			result.close();
		}
		
		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
		}
	return picture;	
}


public Employee UserNameEmployeeCheck(String userName){
	
	try {
		
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement("SELECT * FROM Employee WHERE UserName=? ");
		statment.setString(1,userName);
		ResultSet result = statment.executeQuery();
		if (result.next()){
			employee = new Employee(result.getString(2),result.getString(3),result.getString(4),result.getString(5),result.getString(6),result.getString(7),result.getString(8),result.getString(9),result.getString(10));
			employee.setUserId(result.getInt(1));
			result.close();
		}
		
		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
		}
	return employee;	
}	

public Boolean addClient(Client newClient){
	
	ResultSet Keys = null ;
	try {
		String sql = " INSERT INTO Client (UserName,FirstName,LastName, Email, Password, Mobile)"
				+ 	" VALUES (?,?,?,?,?,?)";
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		
			statment.setString(1,newClient.getUserName());
			statment.setString(2,newClient.getFirstName());
			statment.setString(3,newClient.getLastName());
			statment.setString(4,newClient.getUserEmail());
			statment.setString(5,newClient.getUserPassword());
			statment.setString(6,newClient.getUserMobile());
			
			int affected = statment.executeUpdate();
			if (affected ==1){
				
				Keys = statment.getGeneratedKeys();
				Keys.next();
				int newKey = Keys.getInt(1);
				newClient.setUserId(newKey);
				System.out.println(newKey);
			}
			else{
				
			  return false;
			}
		
		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
	        return false;
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
			return false;
		}
	
	  return true;
}

public Boolean updateClient(Client updateClient){
	

	try {
		String sql = " UPDATE Client SET " + "UserName = ?,FirstName = ?,LastName = ?,Email = ?,Password = ?,Mobile = ?,Address= ?"
				+  "WHERE ClientID = ? ";
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement(sql);
		
			statment.setString(1,updateClient.getUserName());
			statment.setString(2,updateClient.getFirstName());
			statment.setString(3,updateClient.getLastName());
			statment.setString(4,updateClient.getUserEmail());
			statment.setString(5,updateClient.getUserPassword());
			statment.setString(6,updateClient.getUserMobile());
			statment.setString(7, updateClient.getAddress());
			statment.setInt(8, updateClient.getUserId());
			

			int affected = statment.executeUpdate();
			if (affected==1){
				return true;
			}
			else{
				return false;
			}

		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
	        return false;
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
			return false;
		}
	}


public LinkedList<Complaint> HandlingComplaintCheck(String status){
	
	try {
		
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement("SELECT * FROM Complaint WHERE Status=? ");
		statment.setString(1,status);
		ResultSet result = statment.executeQuery();
		while (result.next()){
			complaint = new Complaint(result.getInt(2),result.getInt(3),result.getString(5),result.getString(4),result.getString(6),result.getString(7));
			complaint.setComplaintID(result.getInt(1));
			complaints.add(complaint);
		}
		result.close();
		
		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
		}
	return complaints;	
}	

public Complaint ComplaintIDCheck(int complaintID){
	
	try {
		
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement("SELECT * FROM Complaint WHERE ComplaintID=? ");
		statment.setInt(1,complaintID);
		ResultSet result = statment.executeQuery();
		if (result.next()){
			complaint = new Complaint(result.getInt(2),result.getInt(3),result.getString(5),result.getString(4),result.getString(6),result.getString(7));
			complaint.setComplaintID(result.getInt(1));
			result.close();
		}
		
		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
		}
	return complaint;	
}

public Complaint OrederIDComplaintCheck(int orderID){
	
	try {
		
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement("SELECT * FROM Complaint WHERE OrderID=? ");
		statment.setInt(1,orderID);
		ResultSet result = statment.executeQuery();
		if (result.next()){
			complaint = new Complaint(result.getInt(2),result.getInt(3),result.getString(5),result.getString(4),result.getString(6),result.getString(7));
			complaint.setComplaintID(result.getInt(1));
			result.close();
		}
		
		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
		}
	return complaint;	
}


public Complaint ClientIDComplaintCheck(int clientID){
	
	try {
		
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		PreparedStatement statment = con.prepareStatement("SELECT * FROM Complaint WHERE ClientID=? ");
		statment.setInt(1,clientID);
		ResultSet result = statment.executeQuery();
		if (result.next()){
			complaint = new Complaint(result.getInt(2),result.getInt(3),result.getString(5),result.getString(4),result.getString(6),result.getString(7));
			complaint.setComplaintID(result.getInt(1));
			result.close();
		}
		
		}catch(SQLException ex){
			
			System.out.println("SQLException: " + ex.getMessage());
	        System.out.println("SQLState: " + ex.getSQLState());
	        System.out.println("VendorError: " + ex.getErrorCode());
		
		}catch(Exception ex){
			
			System.out.println(ex.getMessage());
		}
	return complaint;	
}





}
